﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DemoSqlServer.Web
{
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class DataService : IDataService
    {
        static string connectionString = ConfigurationManager.ConnectionStrings["BingMap"].ConnectionString;

        public List<Customer> GetCustomers()
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            DataSet objSet = new DataSet();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = "GetCustomers";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.Fill(objSet);
            

            List<Customer> lstResult = new List<Customer>();
            Customer objCustomer;

            if (objSet.Tables.Count > 0)
            {
                foreach (DataRow dr in objSet.Tables[0].Rows)
                {
                    objCustomer = new Customer();
                    objCustomer.CustomerName = dr["CustomerName"].ToString();
                    objCustomer.CustomerId = Convert.ToInt32(dr["CustomerId"]);
                    objCustomer.Latitude = Convert.ToDouble(dr["Latitude"]);
                    objCustomer.Longitude = Convert.ToDouble(dr["Longitude"]);
                    lstResult.Add(objCustomer);
                }
            }
            return lstResult;
        }
        // Add more operations here and mark them with [OperationContract]

        public List<Customer> GetCustomersByname(String objCustomername)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            DataSet objSet = new DataSet();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = "GetCustomers";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.Fill(objSet);

            List<Customer> lstResult = new List<Customer>();
            Customer objCustomer;

            if (objSet.Tables.Count > 0)
            {
                String strExpr;
                strExpr = "CustomerName='"+ objCustomername + "'";

                foreach (DataRow dr in objSet.Tables[0].Select(strExpr))
                {
                    objCustomer = new Customer();
                    objCustomer.CustomerName = dr["CustomerName"].ToString();
                    objCustomer.CustomerId = Convert.ToInt32(dr["CustomerId"]);
                    objCustomer.Latitude = Convert.ToDouble(dr["Latitude"]);
                    objCustomer.Longitude = Convert.ToDouble(dr["Longitude"]);
                    lstResult.Add(objCustomer);
                }
            }
            return lstResult;
        }

        public List<Customer> UpdateCustomers()
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            DataSet objSet = new DataSet();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = "GetCustomers";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.Fill(objSet);


            List<Customer> lstResult = new List<Customer>();
            Customer objCustomer;

            if (objSet.Tables.Count > 0)
            {
                foreach (DataRow dr in objSet.Tables[0].Rows)
                {
                    objCustomer = new Customer();
                    objCustomer.CustomerName = dr["CustomerName"].ToString();
                    objCustomer.CustomerId = Convert.ToInt32(dr["CustomerId"]);
                    objCustomer.Latitude = Convert.ToDouble(dr["Latitude"]);
                    objCustomer.Longitude = Convert.ToDouble(dr["Longitude"]);
                    lstResult.Add(objCustomer);
                }
            }
            return lstResult;
        }

        public void DoWork()
        {
        }

        public List<Picture> GetImageList()
        {
            //string conn = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Test.MDF;Integrated Security=True;User Instance=True";
            //string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            List<Picture> returnedImageCollection = new List<Picture>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    //cmd.CommandText = "GetAllImages";
                    cmd.CommandText = "Select * From Images";
                    cmd.Connection = con;
                    //cmd.CommandType = System.Data.CommandType.StoredProcedure;                    
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Picture pictures = new Picture();
                        pictures.Name = Convert.ToString(reader["Name"].ToString());
                        pictures.Title = Convert.ToString(reader["Title"]);
                        pictures.Url = Convert.ToString(reader["Url"]);
                        pictures.Description = Convert.ToString(reader["Description"]);
                        returnedImageCollection.Add(pictures);
                    }
                }
                return returnedImageCollection;
            }
        }
       
        public DataSetData GetDataSetData(string SQL, int PageNumber, int PageSize, out CustomException ServiceError)
        {
            try
            {
                if (SQL == String.Empty)
                {
                    SQL = "Select * from tblCustomer";
                }
                DataSet ds = GetDataSet(SQL, PageNumber, PageSize);
                ServiceError = null;
                return DataSetData.FromDataSet(ds);
            }
            catch (Exception ex)
            {
                ServiceError = new CustomException(ex);
            }
            return null;
        }

        private DataSet GetDataSet(string SQL, int PageNumber, int PageSize)
        {
            DataSet ds;
            SqlConnection Connection = new SqlConnection(connectionString);
            try
            {
                Connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(SQL);
                adapter.SelectCommand.Connection = Connection;
                if (PageSize > 0)// Set rowcount =PageNumber * PageSize for best performance
                {
                    long RowCount = PageNumber * PageSize;
                    SqlCommand cmd = new SqlCommand("SET ROWCOUNT " + RowCount.ToString() + " SET NO_BROWSETABLE ON", (SqlConnection)Connection);
                    cmd.ExecuteNonQuery();
                }
                ds = new DataSet();
                adapter.Fill(ds, (PageNumber - 1) * PageSize, PageSize, "Data");
                adapter.FillSchema(ds, SchemaType.Mapped, "DataSchema");
                if (PageSize > 0) // Reset Rowcount back to 0
                {
                    SqlCommand cmd = new SqlCommand("SET ROWCOUNT 0 SET NO_BROWSETABLE OFF", (SqlConnection)Connection);
                    cmd.ExecuteNonQuery();
                }
                if (ds.Tables.Count > 1)
                {
                    DataTable data = ds.Tables["Data"];
                    DataTable schema = ds.Tables["DataSchema"];
                    data.Merge(schema);
                    ds.Tables.Remove(schema);
                }
            }
            catch (Exception err)
            {
                throw err;
            }
            finally
            {
                Connection.Close();
            }
            return ds;
        }

        // REFERENCE FOR SQLDATAADAPTER - UPDATE , INSERT, DELETE, SELECT METHODS

            //        //Connecting database
            //con = new SqlConnection(
            //  "Data Source=mysource;Initial Catalog=mydbname;uid=sa");
            ////create sql adapter for the "emp" table
            //SqlDataAdapter sqlDa = new SqlDataAdapter("select * from emp", con);
            ////create dataset instance
            //DataSet    dSet = new DataSet();
            ////fill the dataset
            //sqlDa.Fill(dSet, "emp");
            ////bind the data grid with the data set
            //dataGrid1.DataSource=dSet.Tables["emp"];

            ////build select command
            //SqlCommand selCmd = new SqlCommand("select * from emp",con);
            //sqlDa.SelectCommand=selCmd;

            ////build insert command
            //SqlCommand insCmd = new SqlCommand(
            //  "insert into emp (Name, Age) values(@Name, @Age)",con);
            //insCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name");
            //insCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
            //sqlDa.InsertCommand = insCmd;

            ////build update command
            //SqlCommand upCmd = new SqlCommand(
            //  "update emp set Name=@Name, Age=@Age where No=@No",con);
            //upCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name");
            //upCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
            //upCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
            //sqlDa.UpdateCommand = upCmd;

            ////build delete command
            //SqlCommand delCmd = new SqlCommand(
            //  "delete from emp where No=@No",con);
            //delCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
            //sqlDa.DeleteCommand = delCmd;

            ////now update the data adapter with dataset.
            //sqlDa.Update(dSet,"emp");

        //private const string connectionString = @"Data Source=LOCALHOST;Initial Catalog=AdventureWorks;Integrated Security=True;";		

       
    }
}
